--
-- Description: Script to save an event record.
-- Steps include creating a new record in the event table if the event is new,
-- or updating a record in the events table for an event that has been updated.
-- Date created: 2 May 2008
--

set ANSI_NULLS on
set QUOTED_IDENTIFIER on
go

--
-- drop the stored procedure if it exists and recreate it.
--
if exists
(
	select * 
	from sys.objects
	where object_id = object_id(N'[saveEvent_sp]')
	and type in (N'P', N'PC')
)
begin
    drop procedure [dbo].[saveEvent_sp]
end
go

create procedure [dbo].[saveEvent_sp]
(
	@FileID int
    , @EventID bigint
	, @EventTypeName nvarchar(50)
	, @DateCreated smalldatetime
	, @DateModified smalldatetime
	, @TotalHits bigint
    , @ApplicationName nvarchar(400)
	, @ApplicationVersion nvarchar(24)
    , @ApplicationStampDate datetime = null
    , @ModuleName nvarchar(400)
	, @ModuleVersion nvarchar(24)
    , @ModuleStampDate datetime = null
	, @Offset nvarchar(50)
	, @ExceptionCode char(10) = null
)
as
begin
	declare @EventTypeID smallint

	--
	-- get the EventTypeID from the event type name.
	--
	exec @EventTypeID = [dbo].[SaveEventType_sp] 
						@EventTypeName = @EventTypeName
	
	--
	-- check if the event id and event type id combination exists
	-- in the Events table.
	--
	if not exists
	(
		select top 1 1
		from dbo.Events
		where EventID = @EventID
		and EventTypeID = @EventTypeID
	)
	begin
		--
		-- create a new record in the Events table.
		--
		insert into [dbo].[Events]
		(
			[EventID]
			, [EventTypeID]
			, [DateCreated]
			, [DateModified]
			, [TotalHits]
			, [ApplicationName]
			, [ApplicationVersion]
			, [ApplicationStampDate]
			, [ModuleName]
			, [ModuleVersion]
			, [ModuleStampDate]
			, [Offset]
			, [ExceptionCode]
		)
		values
		(
			@EventID
			, @EventTypeID
			, @DateCreated
			, @DateModified
			, @TotalHits
			, @ApplicationName
			, @ApplicationVersion
			, @ApplicationStampDate
			, @ModuleName
			, @ModuleVersion
			, @ModuleStampDate
			, @Offset
			, @ExceptionCode
		)
	end
	else
	begin
		--
		-- Update the record for the EventID/EventTypeID combination.
		-- The only updates can be TotalHits and DateModified.
		--
		update [dbo].[Events]
		set TotalHits = @TotalHits
			, DateModified = @DateModified
		where @DateModified > DateModified
		and EventID = @EventID
		and EventTypeID = @EventTypeID
	end

	--
	-- check if the combination of FileID, EventID, EventTypeID
	-- exists in the FilesEvents table.
	--
	if not exists
	(
		select top 1 1
		from [dbo].[FilesEvents]
		where FileID = @FileID
		and EventID = @EventID
		and EventTypeID = @EventTypeID
	)
	begin
		--
		-- insert a new record in the FilesEvents table.
		--
		insert into [dbo].[FilesEvents]
		(
			FileID
			, EventID
			, EventTypeID
		)
		values
		(
			@FileID
			, @EventID
			, @EventTypeID
		)
	end
end
go

set ansi_nulls off
go
set quoted_identifier off
go
